/*
 * Copyright (c) 2012 Andrea De Rinaldis <ozioso at ipignoli.dyndns.org>
 * 
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 * 
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */

package org.dyndns.ipignoli.droidcruise.db;

import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import org.dyndns.ipignoli.droidcruise.R;
import org.dyndns.ipignoli.droidcruise.position.MyCoordinate;
import org.dyndns.ipignoli.droidcruise.position.MyLocation;
import org.dyndns.ipignoli.droidcruise.recording.Recording;
import org.dyndns.ipignoli.droidcruise.recording.RouteRecording;
import org.dyndns.ipignoli.droidcruise.recording.Waypoint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.CursorIndexOutOfBoundsException;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class MyHelper extends SQLiteOpenHelper{
  public static final String  TABLE_RECORDINGS         = "recordings";
  public static final String  F_RECORDINGS_ID          = "_id";
  public static final String  F_RECORDINGS_TYPE        = "type";
  public static final String  F_RECORDINGS_TIME        = "time";
  public static final String  F_RECORDINGS_LOCALITY    = "locality";
  public static final String  F_RECORDINGS_LOCALITY_OK = "locality_ok";
  public static final String  F_RECORDINGS_LAT         = "lat";
  public static final String  F_RECORDINGS_LONG        = "long";
  public static final String  F_RECORDINGS_LIMIT       = "speed_limit";
  public static final String  F_RECORDINGS_COMMENT     = "comment";
  public static final String  F_RECORDINGS_SENT        = "sent";

  public static final String  TABLE_WAYPOINTS          = "waypoints";
  public static final String  F_WAYPOINTS_ID           = "_id";
  public static final String  F_WAYPOINTS_RECORDING_ID = "recording_id";
  public static final String  F_WAYPOINTS_TIME         = "time";
  public static final String  F_WAYPOINTS_LAT          = "lat";
  public static final String  F_WAYPOINTS_LONG         = "long";

  protected static final int  DATABASE_VERSION         = 2;

  private static final String CREATE_RECORDINGS        =
                                                           " ("
                                                               + F_RECORDINGS_ID
                                                               + " integer primary key autoincrement, "
                                                               + F_RECORDINGS_TYPE
                                                               + " integer not null, "
                                                               + F_RECORDINGS_TIME
                                                               + " integer not null, "
                                                               + F_RECORDINGS_LOCALITY
                                                               + " text not null, "
                                                               + F_RECORDINGS_LOCALITY_OK
                                                               + " integer not null, "
                                                               + F_RECORDINGS_LAT
                                                               + " real not null, "
                                                               + F_RECORDINGS_LONG
                                                               + " real not null, "
                                                               + F_RECORDINGS_LIMIT
                                                               + " integer not null, "
                                                               + F_RECORDINGS_COMMENT
                                                               + " text not null, "
                                                               + F_RECORDINGS_SENT
                                                               + " integer not null"
                                                               + " );";

  private static final String CREATE_WAYPOINTS         =
                                                           " ("
                                                               + F_WAYPOINTS_ID
                                                               + " integer primary key autoincrement, "
                                                               + F_WAYPOINTS_RECORDING_ID
                                                               + " integer not null, "
                                                               + F_WAYPOINTS_TIME
                                                               + " integer not null, "
                                                               + F_WAYPOINTS_LAT
                                                               + " real not null, "
                                                               + F_WAYPOINTS_LONG
                                                               + " real not null"
                                                               + " );";

  private Context             context;

  public MyHelper(Context context){
    super(context,
        context.getResources().getText(R.string.database).toString(), null,
        DATABASE_VERSION);
    this.context = context;
  }

  @Override
  public void onCreate(SQLiteDatabase db){
    db.execSQL("CREATE TABLE " + TABLE_RECORDINGS + CREATE_RECORDINGS);
    db.execSQL("CREATE TABLE " + TABLE_WAYPOINTS + CREATE_WAYPOINTS);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
    switch(oldVersion){
      case 1:
        upgradeFrom1To2(db);
    }
  }

  public long insertRecording(int type, MyLocation location, boolean sent){
    return getWritableDatabase().insert(
        TABLE_RECORDINGS,
        null,
        createContentValues(new Recording(-1, type, location,
            Recording.DEFAULT_LIMIT, "", sent)));
  }

  public long insertRouteRecording(List<MyCoordinate> locations, boolean sent){
    if(locations.size() == 0)
      return -1l;
    long id =
        getWritableDatabase().insert(
            TABLE_RECORDINGS,
            null,
            createContentValues(new RouteRecording(-1, locations.get(0),
                locations.get(locations.size() - 1), Recording.DEFAULT_LIMIT,
                "", sent)));
    for(Iterator<MyCoordinate> it = locations.iterator(); it.hasNext();)
      getWritableDatabase().insert(TABLE_WAYPOINTS, null,
          createWaypointContentValues(id, it.next()));
    return id;
  }

  public Cursor fetchRecordings(){
    return getWritableDatabase().query(TABLE_RECORDINGS, null, null, null,
        null, null, F_RECORDINGS_TIME + " DESC");
  }

  public Recording fetchRecording(long id) throws SQLException{
    Cursor cursor =
        getWritableDatabase().query(true, TABLE_RECORDINGS, null,
            F_RECORDINGS_ID + "=" + id, null, null, null, null, null);

    if(cursor == null)
      return null;

    cursor.moveToFirst();

    return new Recording(cursor.getLong(0), cursor.getInt(1),
        cursor.getLong(2), cursor.getString(3), cursor.getInt(4) == 1 ? true
            : false, cursor.getDouble(5), cursor.getDouble(6),
        cursor.getInt(7), cursor.getString(9), cursor.getInt(8) == 1 ? true
            : false);
  }

  public List<MyCoordinate> fetchWaypoints(long routeId){
    List<MyCoordinate> waypoints = new LinkedList<MyCoordinate>();

    Cursor cursor =
        getWritableDatabase().query(true, TABLE_WAYPOINTS, null,
            F_WAYPOINTS_RECORDING_ID + "=" + routeId, null, null, null, null,
            null);
    while(!cursor.isLast()){
      cursor.moveToNext();
      waypoints.add(fetchWaypoint(cursor.getLong(cursor
          .getColumnIndex(F_WAYPOINTS_ID))));
    }

    return waypoints;
  }

  public Waypoint fetchWaypoint(long id){
    Cursor cursor =
        getWritableDatabase().query(true, TABLE_WAYPOINTS, null,
            F_WAYPOINTS_ID + "=" + id, null, null, null, null, null);

    if(cursor == null)
      return null;

    cursor.moveToFirst();

    return new Waypoint(cursor.getLong(0), cursor.getLong(1),
        cursor.getLong(2), cursor.getDouble(3), cursor.getDouble(4));
  }

  public void updateRecordings() throws SQLException{
    Cursor cursor = fetchRecordings();
    while(!cursor.isLast()){
      try{
        cursor.moveToNext();
        updateRecording(fetchRecording(cursor.getLong(cursor
            .getColumnIndex(F_RECORDINGS_ID))));
      }catch(CursorIndexOutOfBoundsException e){
        return;
      }
    }
  }

  public void updateRecordingSent(long id, boolean sent) throws SQLException{
    Recording recording = fetchRecording(id);
    recording.setSent(sent);
    updateRecording(recording);
  }

  public boolean deleteRecording(long id, int type){
    if(getWritableDatabase().delete(TABLE_RECORDINGS,
        F_RECORDINGS_ID + "=" + id, null) <= 0)
      return false;

    if(type == Recording.ROUTE_TYPE)
      deleteWaypoints(id);

    return true;
  }

  private boolean updateRecording(Recording recording){
    return getWritableDatabase().update(TABLE_RECORDINGS,
        createContentValues(recording),
        F_RECORDINGS_ID + "=" + recording.getId(), null) > 0;
  }

  private void deleteWaypoints(long routeId){
    getWritableDatabase().delete(TABLE_WAYPOINTS,
        F_WAYPOINTS_RECORDING_ID + "=" + routeId, null);
  }

  private ContentValues createContentValues(Recording recording){
    ContentValues values = new ContentValues();
    values.put(F_RECORDINGS_TYPE, recording.getType());
    values.put(F_RECORDINGS_TIME, recording.getTime());
    values.put(F_RECORDINGS_LOCALITY, recording.getLocality());
    values.put(F_RECORDINGS_LOCALITY_OK, recording.getLocalityOK() ? 1 : 0);
    values.put(F_RECORDINGS_LAT, recording.getLatitude());
    values.put(F_RECORDINGS_LONG, recording.getLongitude());
    values.put(F_RECORDINGS_LIMIT, recording.getLimit());
    values.put(F_RECORDINGS_COMMENT, recording.getComment());
    values.put(F_RECORDINGS_SENT, recording.getSent() ? 1 : 0);
    return values;
  }

  private ContentValues createWaypointContentValues(long routeId,
      MyCoordinate location){
    ContentValues values = new ContentValues();
    values.put(F_WAYPOINTS_RECORDING_ID, routeId);
    values.put(F_WAYPOINTS_TIME, location.getTime());
    values.put(F_WAYPOINTS_LAT, location.getLatitude());
    values.put(F_WAYPOINTS_LONG, location.getLongitude());
    return values;
  }

  private void upgradeFrom1To2(SQLiteDatabase db){
    db.execSQL("ALTER TABLE " + TABLE_RECORDINGS + " ADD COLUMN "
        + F_RECORDINGS_COMMENT + " text not null default \"\"");
  }
}
